## Create panel (b) of Figure 3

## Loading packages
library(tidyverse)
library(sf)
library(foreign)
library(readxl)
library(scales)

## Load school boundaries
es1112 = st_read("attende1112.shp")

## Load list of LAUSD schools -- keep just district 6
schools = read_excel("school_ids.xlsx") %>%
  mutate(district = str_trim(district),
         district = as.numeric(district)) %>%
  filter(district %in% c(6))

## Load school identifiers
es1112_keys = read.dbf("attende1112_codes.dbf") %>%
  select(SCHOOL_ID, NAME, KEY_) %>%
  filter(SCHOOL_ID %in% schools$school_id) ## Keep just schools in 6

## Aggregate boundaries to the school-levels
## Some schools are split into separate geographic boundaries
es1112 = es1112 %>%
  left_join(., es1112_keys, by = "KEY_") %>%
  filter(!is.na(SCHOOL_ID)) %>% ## Drop if not in district 6 
  group_by(SCHOOL_ID, NAME) %>% 
  summarise(geometry = st_union(geometry)) %>%
  mutate(SCHOOL_ID = as.numeric(as.character(SCHOOL_ID)))

## Merge in votes in 2017 election for each school
load("dta.dta")

votes = dta %>%
  filter(election == 2017 & district == 6 & grade == "es"
         & school_year == 1718) %>%
  select(SCHOOL_ID, top_two_winner_pct)

## Merge in BMP projects 
## Assign = 1 if received a project over four year period
bmps = dta %>%
  filter(election == 2017 & district == 6 & grade == "es") %>%
  group_by(SCHOOL_ID) %>%
  summarise(bmp_received = sum(bmp_received)) %>%
  mutate(bmp_received = case_when(bmp_received >= 1 ~ 1,
                                  TRUE ~ 0)) %>%
  select(SCHOOL_ID, bmp_received)

## Merge in votes and BMPs data with the school shapefile
es1112 = es1112 %>%
  left_join(., votes, by = "SCHOOL_ID") %>%
  left_join(., bmps, by = "SCHOOL_ID") %>%
  left_join(., schools %>% select(school_id, district), by = c("SCHOOL_ID" = "school_id"))

## Create plot
figure3b = ggplot(data = es1112 %>% filter(!is.na(top_two_winner_pct))) +
  geom_sf(aes(fill = I(top_two_winner_pct/100)), 
          size = 0.3, color = NA) +
  geom_sf(
    data = . %>% filter(bmp_received == 1),
    fill=NA, colour="black",
    size=1,
    inherit.aes=FALSE) +
  scale_fill_gradientn(name = "Vote Share", colours = colorspace::diverge_hcl(6),
                       labels = percent, n.breaks = 5) +
  coord_sf(datum = NA) +
  theme_classic()# +
